﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using DBBuilder.DBInteraction;
using DBBuilder.DBDataStructure;
using CommonLogging;
using Microsoft.SqlServer.Management.Smo;
using ForeignKey = DBBuilder.DBDataStructure.ForeignKey;

namespace DBBuilder
{
    /// <summary>
    /// Initialize data context
    /// </summary>
    public class DataContext
    {
        #region Properties & Fields

        /// <summary>
        /// The DBDataProvider instance
        /// </summary>
        private readonly DBDataProvider _provider;

        /// <summary>
        /// The logger instance
        /// </summary>
        private static readonly ILogger Logger = LoggerManager.GetLogger(typeof(DataContext).Name);

        #endregion

        #region Constructors

        /// <summary>
        /// Initialize instance of AdminDataContext with datasource and database name as inputs
        /// </summary>
        public DataContext(string dataSource, string dataBaseName, string userName, string password)
        {
            _provider = new DBDataProvider(dataSource, dataBaseName, userName, password);
        }

        #endregion

        #region Methods

        /// <summary>
        /// Only for testing log
        /// </summary>
        public void TestLog()
        {
            Logger.Info("Test log");
        }

        /// <summary>
        /// Inititalize database structure
        /// </summary>
        /// <returns></returns>
        public DataBase InitDBStructure()
        {
            DataBase db = null;
            // Step1: init db structure
            var tbls = this.GetDataTables();
            if(tbls.Any())
            {
                db = new DataBase(tbls);
                // Step2: update keys
                this.SetKeys(db);
            }   
            return db;
        }

        /// <summary>
        /// Get list of data tables
        /// </summary>
        /// <returns></returns>
        private IList<DataTable> GetDataTables()
        {
            var lstDataTbl = new List<DataTable>();

            // Get list of table from DB
            var lstTable = GetTables();

            foreach(string table in lstTable)
            {
                var tblComp = table.Split(new char[] { '_' });

                if ((!"aspnet".Equals(tblComp[0]) && !"sysdiagrams".Equals(table)) || "aspnet_Users".Equals(table))
                {
                    var ds = new DataSet();

                    _provider.Query = string.Format("Select * From [{0}] ", table);
                    _provider.FillDataSet(ref ds, table);

                    if (ds.Tables[table] != null)
                    {
                        lstDataTbl.Add(ds.Tables[table]);
                    }
                }
            }

            return lstDataTbl;
        }

        /// <summary>
        /// Get list of tables of database
        /// </summary>
        /// <returns></returns>
        private IList<string> GetTables()
        {
            var ds = new DataSet();
            _provider.Query = @"select table_name as Name from
                                INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";
            _provider.FillDataSet(ref ds, "ListTable");
            return ConvertDataTable2List(ds.Tables["ListTable"]);
        }

        /// <summary>
        /// Convert DataTable to list
        /// </summary>
        /// <param name="dt">DataTale input</param>
        /// <returns></returns>
        private IList<string> ConvertDataTable2List(DataTable dt)
        {
            return (from DataRow row in dt.Rows select row[0].ToString()).ToList();
        }

        /// <summary>
        /// Get list of primary keys of specified table
        /// </summary>
        /// <param name="smoTbl"></param>
        /// <returns></returns>
        private IList<PrimaryKey> GetPKs(Microsoft.SqlServer.Management.Smo.Table smoTbl)
        {
            var columns = smoTbl.Columns;
            return (from Column col in columns
                    where col.InPrimaryKey
                    select col.Identity
                               ? new PrimaryKey {Name = col.Name, IsIdentify = true}
                               : new PrimaryKey {Name = col.Name, IsIdentify = false}).ToList();
        }

        /// <summary>
        /// Update foreign keys and primary keys for all tables of database
        /// </summary>
        /// <param name="DB"></param>
        public void SetKeys(DataBase DB)
        {
            foreach(DBDataStructure.Table table in DB.Tables)
            {
                Logger.InfoFormat("Set keys for table ", table.Name);

                // Build a "serverConnection" with the information of the "sqlConnection"
                var serverConnection =
                    new Microsoft.SqlServer.Management.Common.ServerConnection(_provider.ObjConnection);

                // The "serverConnection is used in the ctor of the Server.
                var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);

                var db = server.Databases[_provider.DataBaseName];

                // Get foreign key list of corresponding table
                var smoTbl = db.Tables[table.Name];
                var fks = GetFKs(smoTbl);

                // Update foreign keys
                foreach(DBDataStructure.Attribute attr in table.Attributes)
                {
                    var referTo = string.Empty;
                    if (IsFK(fks, attr.Name, ref referTo))
                    {
                        attr.IsForeignKey = true;
                        attr.ReferTo = referTo;
                    }
                }

                // Update primary keys
                var lstPK = GetPKs(smoTbl);
                foreach(DBDataStructure.Attribute attr in table.Attributes)
                {
                    var isIdentify = false;
                    if(IsPK(lstPK, attr.Name, ref isIdentify)){
                        attr.IsPrimaryKey = true;
                        attr.IsIdentify = isIdentify;
                    }
                }
            }
        }

        /// <summary>
        /// Checking if attribute is foreign key 
        /// </summary>
        /// <param name="fks">List of foreign keys which received before</param>
        /// <param name="attrName">Attribute to check if it is foreign key</param>
        /// <param name="referTo">Table that foreign key refer to</param>
        /// <returns></returns>
        private bool IsFK(IList<ForeignKey> fks, string attrName, ref string referTo)
        {
            var result = false;
            foreach(ForeignKey fk in fks)
            {
                if (fk.Name.Any(key => key.Equals(attrName)))
                {
                    referTo = fk.ReferTo;
                    result = true;
                    break;
                }
            }
            return result;
        }

        /// <summary>
        /// Checking if attribute is primary key 
        /// </summary>
        /// <param name="pks">List of primary keys which received before</param>
        /// <param name="attrName">Attribute to check if it is primary key</param>
        /// <param name="isIdentify">Table that primary key refer to</param>
        /// <returns></returns>
        private bool IsPK(IList<PrimaryKey> pks, string attrName, ref bool isIdentify)
        {
            var result = false;
            foreach (PrimaryKey pk in pks)
            {
                if (pk.Name.Equals(attrName))
                {
                    isIdentify = pk.IsIdentify;
                    result = true;
                }
            }
            return result;
        }

        /// <summary>
        /// Get list of SMO foreibn keys from SMO table
        /// </summary>
        /// <param name="tbl"></param>
        /// <returns></returns>
        private IList<ForeignKey> GetFKs(Microsoft.SqlServer.Management.Smo.Table tbl)
        {
            return (from Microsoft.SqlServer.Management.Smo.ForeignKey fk in tbl.ForeignKeys
                    select new ForeignKey
                               {
                                   Name = GetFKNames(fk),
                                   ReferTo = fk.ReferencedTable
                               }).ToList();
        }

        /// <summary>
        /// Retrieve list of foreign keys from SMO ForeignKey
        /// </summary>
        /// <param name="fk"></param>
        /// <returns>List of foreign keys</returns>
        private IList<string> GetFKNames(Microsoft.SqlServer.Management.Smo.ForeignKey fk)
        {
            var result = (from ForeignKeyColumn key in fk.Columns select key.Name).ToList();
            return result;
        }

        #endregion
    }
}
